#!/usr/bin/env python
# coding=utf-8
"""
@Author  : youjia - 卞志伟
@file    : week_report_super_progress.py
@contact : bianzhiwei@iyoujia.com
@time    : 2019-06-27 15:26 
@Desc    : 超赞房东进度数据
@Software: PyCharm
"""
import os
import sys

# 当前文件的路径
pwd, filename = os.path.split(os.path.abspath(__file__))
# 当前文件的父路径
father_path = os.path.abspath(os.path.dirname(pwd) + os.path.sep + ".")
# 当前文件的前两级目录
grader_father = os.path.abspath(os.path.dirname(pwd) + os.path.sep + "..")
sys.path.append(pwd)
sys.path.append(father_path)
sys.path.append(grader_father)

import json
import datetime
import calendar
import pandas as pd
import pymongo
from report_system.utils import db_util
from report_system.utils import mail_util
from report_system.utils import mailbox_util

from report_system.utils.development_util import debug

if debug:
    receivers = 'dt@iyoujia.com;jiangxiaoyan@iyoujia.com'
    city_mails = mailbox_util.mails_test
else:
    receivers = 'dt@iyoujia.com;yy@iyoujia.com;jiangxiaoyan@iyoujia.com'
    city_mails = None


def label_type_str(t):
    if t == 'percent':
        return '%'
    elif t == 'yearly':
        return '/年'
    elif t == 'star':
        return '星'
    return ''


def process_airbnb():
    yj_user_sql = """select user_id, mail from youjia.airbnb_token group by user_id"""
    yj_user_df = pd.read_sql_query(yj_user_sql, db_util.youjia_13_sjz_db)
    df_dict = {}
    user_id_mail = {}
    for idx, row in yj_user_df.iterrows():
        user_id_mail[row['user_id']] = row['mail']
    records = list()
    listing_count = dict()
    airbnb_listing_sql = """select any_value(account_id) account_id, count(*) count 
                from youjia_tpp.airbnb_listing 
                where state=0 
                and listing->>'$.listing_state'='active' 
                group by any_value(account_id)"""
    airbnb_listing_df = pd.read_sql_query(airbnb_listing_sql, db_util.youjia_13_sjz_db)

    for idx, row in airbnb_listing_df.iterrows():
        listing_count[row['account_id']] = row['count']

    airbnb_superhost_sql = """select airbnb_superhost.*,
             airbnb_account.city_name 城市, 
             airbnb_account.email 邮箱 ,
             uo.name 门店
            from youjia_tpp.airbnb_superhost 
            left join youjia_tpp.airbnb_account on airbnb_account.user_id=airbnb_superhost.user_id 
            left join youjia.airbnb_token at1 on at1.user_id = airbnb_account.user_id
            left join youjia.user_org uo on uo.id =  at1.work_id
            where  airbnb_account.id is not null and airbnb_account.state = 1 order by airbnb_account.city_name"""

    airbnb_superhost_df = pd.read_sql_query(airbnb_superhost_sql, db_util.youjia_13_sjz_db)

    # 城市， 账号，名下房源套数 是否是超赞房东，总体评分 、评价总数、5星评价、回复率、已接待行程、 退订次数、已评价总数、已评价行程、待改进事项、超赞房东评估时间
    for idx, row in airbnb_superhost_df.iterrows():
        js = json.loads(row['for_web_js'])
        user = js['bootstrapData']['user'] if 'bootstrapData' in js else None
        stats = json.loads(row['for_web_host_stats'])
        record = dict(
            {'城市': '', '门店': '', '邮箱': '', '直连': '', '房东名': '', '房源数': 0, '是否超赞房东': False, '总体评分': 0, '评价总数': 0,
             '5星评价数': 0, '5星评价率': 0, '回复率': 0, '已接待住宿': 0, '退订次数': 0, '本次评估时间': '',
             '下次评估时间': '', '待改进事项': '', '值得肯定的方面': ''})
        record['城市'] = row['城市']
        record['门店'] = row['门店']
        # record['邮箱'] = user['email'] if user and row['user_id'] not in user_id_mail else user_id_mail[row['user_id']] if row['user_id'] in user_id_mail else None
        record['邮箱'] = user['email']
        # record['直连'] = '是' if row['user_id'] in user_id_mail else ''
        record['直连'] = '是'
        record['房东名'] = user['first_name'] if user else None
        record['房源数'] = listing_count[row['account_id']] if row['account_id'] in listing_count else 0
        record['是否超赞房东'] = stats['superhost']['superhost']
        record['总体评分'] = user['average_decimal_host_rating'] if user else None
        record['评价总数'] = user['num_host_ratings'] if user else None
        record['5星评价数'] = user['host_five_star_reviews'] if user else None
        record['5星评价率'] = stats['superhost']['five_star_review_rate']
        # record['回复率'] = js['']
        # 已接待行程
        # 退订次数
        record['本次评估时间'] = stats['superhost']['last_eligibility_date']
        record['下次评估时间'] = stats['superhost']['next_eligibility_date']
        # 待改进事项
        record['待改进事项'] = ''
        js = js['bootstrapData']
        for r in js['hostingInsightsData']['programsData']['host_stat']['incomplete_requirements']:
            if 'optional_requirements' in r and (
                    r['optional_requirements'] is None or len(r['optional_requirements']) <= 0):
                record['待改进事项'] += r['localized_title'] + ': ' + r['host_progress'] + label_type_str(r['label_type']) + \
                                   ' 目标: ' + r['threshold'] + label_type_str(r['label_type']) + '\n'
            else:
                record['待改进事项'] += r['localized_title'] + ': \n'
                for r0 in r['optional_requirements'] if 'optional_requirements' in r else []:
                    record['待改进事项'] += r0['title'] + ':' + r0['progress'] + label_type_str(r['label_type']) + \
                                       ' 目标: ' + r0['threshold'] + label_type_str(r['label_type']) + '\n'
            if r['localized_title'] == '回复率':
                record['回复率'] = int(r['host_progress'])
            if r['localized_title'] == '已接待住宿':
                record['已接待住宿'] = r['host_progress']
            if r['localized_title'] == '取消预订次数':
                record['退订次数'] = int(r['host_progress'])
            if r['localized_title'] == '总体评分':
                record['总体评分'] = r['host_progress']
        if record['待改进事项'].endswith('\n'):
            record['待改进事项'] = record['待改进事项'][0:-1]

        # 值得肯定的方面
        record['值得肯定的方面'] = ''
        for r in js['hostingInsightsData']['programsData']['host_stat']['complete_requirements']:
            if 'optional_requirements' in r and (
                    r['optional_requirements'] is None or len(r['optional_requirements']) <= 0):
                record['值得肯定的方面'] += r['localized_title'] + ': ' + r['host_progress'] + label_type_str(
                    r['label_type']) + \
                                     ' 目标: ' + r['threshold'] + label_type_str(r['label_type']) + '\n'
            else:
                record['值得肯定的方面'] += r['localized_title'] + ': \n'
                for r0 in r['optional_requirements'] if 'optional_requirements' in r else []:
                    record['值得肯定的方面'] += r0['title'] + ':' + r0['progress'] + label_type_str(r['label_type']) + \
                                         ' 目标: ' + r0['threshold'] + label_type_str(r['label_type']) + '\n'
            if r['localized_title'] == '回复率':
                record['回复率'] = int(r['host_progress'])
            if r['localized_title'] == '已接待住宿':
                record['已接待住宿'] = r['host_progress'][:-1]
            if r['localized_title'] == '取消预订次数':
                record['退订次数'] = int(r['host_progress'])
            if r['localized_title'] == '总体评分':
                record['总体评分'] = r['host_progress']
        if record['值得肯定的方面'].endswith('\n'):
            record['值得肯定的方面'] = record['值得肯定的方面'][0:-1]

        records.append(record)

    df_dict['Airbnb超赞房东'] = pd.DataFrame(records)

    # 进度数据
    type_name = {'basic': '基本', 'work': '商务差旅', 'family': '家庭出游', 'select': 'PLUS'}
    status_name = {'ineligible': '无资格', 'incomplete': '未完成', 'complete': '已完成'}

    # 查询有家库在线状态
    listing_online = dict()
    airbnb_upload_lodge_sql = """select 
    t.*,  
    if(lodge.state_super=10 and lodge.state=0, '在线', '下线') is_online
    from (
        select t.listing_id, t.lodge_id 
        from youjia.airbnb_upload_lodge t,lodge d 
        where t.lodge_id=d.id 
        and d.state=0 
        and state_super=10 
        and t.listing_id is not null) t
    left join lodge on lodge.id=t.lodge_id"""
    airbnb_upload_lodge_df = pd.read_sql_query(airbnb_upload_lodge_sql, db_util.youjia_13_sjz_db)
    for idx, row in airbnb_upload_lodge_df.iterrows():
        listing_online[row['listing_id']] = row['is_online']

    airbnb_progress_sql = """select 
        aa.city_name 城市,
        aa.email 邮箱,
        ap.user_id 账户id,
        aa.first_name 账户名,
        ap.listing_id 房源id,
        if(al.id is not null, al.listing->>'$.listing_state', '') 状态,
        ap.status,
        ap.items
        from youjia_tpp.airbnb_progress ap
        left join youjia_tpp.airbnb_account aa on aa.user_id=ap.user_id
        left join youjia_tpp.airbnb_listing al on al.listing_id=ap.listing_id
        where  ap.state=0 and aa.state = 1
        order by aa.city_name, ap.id"""
    airbnb_progress_df = pd.read_sql_query(airbnb_progress_sql, db_util.youjia_13_sjz_db)
    progress_records = []
    for idx, row in airbnb_progress_df.iterrows():
        record = dict(row)
        record['直连'] = '是' if row['账户id'] in user_id_mail else ''
        record['有家在线'] = listing_online[row['房源id']] if row['房源id'] in listing_online else ''
        status = json.loads(row['status'])
        items = json.loads(row['items'])
        for k in status.keys():
            t = type_name[k] if k in type_name else k
            record[t + '状态'] = status[k] if status[k] not in status_name else status_name[status[k]]
            item_names = ['待改进事项', '值得肯定的方面']
            for item_name in item_names:
                record[t + item_name] = ''
                requirements = 'incomplete_requirements' if item_name == '待改进事项' else 'complete_requirements'
                if k not in items or requirements not in items[k]:
                    continue
                for r in items[k][requirements]:
                    record[t + item_name] += r['localized_title'] + ': ' + r['host_progress'] + label_type_str(
                        r['label_type']) + \
                                             ' 目标: ' + r['threshold'] + label_type_str(r['label_type']) + '\n'
                if record[t + item_name].endswith('\n'):
                    record[t + item_name] = record[t + item_name][0:-1]
        record.pop('status')
        record.pop('items')
        progress_records.append(record)
    df_dict['Airbnb房源进度'] = pd.DataFrame(progress_records)
    return df_dict


def col_name(col_ext, name, add=True):
    i = 0 if name not in col_ext else col_ext[name]
    if add:
        col_ext[name] = i + 1
    else:
        i = i - 1
    return '%s_%d' % (name, i) if i > 0 else ('%s' % name)


def process_zg():
    units = list()
    col = db_util.mongodb_conn('ctrip', 'zhenguo_landlord', db_util.mongodb_url_)
    for r in col.find({'enable': True, 'login': {'$exists': True},
                       'awesome_update_time': {'$gt': datetime.datetime.now() - datetime.timedelta(days=3)}},
                      {'account': 1, 'awesome': 1},
                      sort=[('account.城市', pymongo.ASCENDING), ('account.门店名称', pymongo.ASCENDING)]):
        unit = {}
        col_ext = {}
        unit['城市'] = r['account']['城市']
        unit['门店'] = r['account']['门店名称']
        unit['账号'] = r['account']['榛果账号']

        # thisMonth = r['awesome']['initState']['thisMonth' ]
        # nextMonth = r['awesome']['initState']['nextMonth' ]
        for month in ['本月', '下月']:
            try:
                print(r)
                data = r['awesome']['initState']['thisMonth' if month == '本月' else 'nextMonth']
                quota = data['quota']
                std = data['standard']
                unit[col_name(col_ext, month + '评估状态')] = (
                    '已通过' if data['isSuperHost'] == 1 else '未通过') if month == '本月' else '进行中'
                start_d = datetime.datetime.strptime(str(data['quotaStartDate']),
                                                     '%Y%m%d') if 'quotaStartDate' in data else datetime.datetime.now()
                end_d = datetime.datetime.strptime(str(data['quotaEndDate']),
                                                   '%Y%m%d') if 'quotaEndDate' in data else datetime.datetime.now()

                if month == '下月':  # 月最后一天
                    end_d = end_d.replace(day=calendar.monthrange(end_d.year, end_d.month)[1])
                unit[col_name(col_ext, month + '评估周期')] = '%s — %s' % (start_d.date(), end_d.date())
                unit[col_name(col_ext, month + '更新时间')] = '%s 09:00' % (end_d.date() + datetime.timedelta(days=1))
                unit[col_name(col_ext, month + '完成间夜数')] = quota['consumptionRoomNights']
                unit[col_name(col_ext, month + '完成间夜数目标')] = '≥%d' % std['consumptionRoomNights']
                unit[col_name(col_ext, month + '拒单次数')] = quota['rejectOrderCount']
                unit[col_name(col_ext, month + '拒单目标')] = '=%d' % std['rejectOrderCount']
                unit[col_name(col_ext, month + '回复率')] = float(quota['replySessionRate']) / 10000
                unit[col_name(col_ext, month + '回复率目标')] = '≥%.0f%%' % (float(std['replySessionRate']) / 100)
                unit[col_name(col_ext, month + '回复时长（分钟）')] = quota['replyTime']
                unit[col_name(col_ext, month + '回复时长目标')] = '≤%d' % std['replyTime']
                unit[col_name(col_ext, month + '好评率')] = float(
                    quota['guCommentRate']) / 10000 if 'guCommentRate' in quota else '-'
                unit[col_name(col_ext, month + '好评率目标')] = '≥%.0f%%' % (float(std['guCommentRate']) / 100)
                unit[col_name(col_ext, month + '表现分')] = quota['hostScore']
                unit[col_name(col_ext, month + '表现分目标')] = '=%d' % std['hostScore']
            except:
                pass
        units.append(unit)
    df = pd.DataFrame(units)
    return {'榛果超赞房东': df}


def process():
    df_dict = dict()
    df_dict.update(process_airbnb())
    df_dict.update(process_zg())

    text = """    Dear All:
                        附件是Airbnb、榛果账号情况！ 
                        请查收！
                        谢谢！
                如有问题！
                请联系: dt@iyoujia.com
                """
    mail_util.distribute_mail(df_dict=df_dict, file_name='超赞房东', text=text, index=False,
                              title="超赞房东、房源进度数据", colors_dict=None, receiver=receivers,
                              city_mails=city_mails, city=True, city_key='城市', na_rep='-', uuid='house')


if __name__ == '__main__':
    process()
    # a = {'a':1,'B':2}
    # a.pop('a')
    # print(a)
